package cn.hotel.mapper;

import cn.hotel.pojo.HouseType;
import cn.hotel.pojo.Member;
import cn.hotel.pojo.OrderManagement;
import cn.hotel.pojo.RoomKeeper;
import cn.hotel.utlis.Crud;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class OrderFormMapperImpl extends Crud implements OrderFormMapper {

    //展示房型
    @Override
    public List<HouseType> show() {
        String sql = "SELECT ID,HOUSETYPE,PRICE,PARTICULARS,HOUSENUM FROM HOUSE_TYPE";
        List<Object> param = new ArrayList<>();
        ResultSet resultSet = this.getResultSet(sql,param);
        List<HouseType> houseTypeList = null;
        try {
            houseTypeList = new ArrayList<>();
            HouseType houseType = null;
            while (resultSet.next()){
                houseType = new HouseType();
                houseType.setId(resultSet.getLong(1));
                houseType.setHouseType(resultSet.getString(2));
                houseType.setPrice(resultSet.getString(3));
                houseType.setParticulars(resultSet.getString(4));
                houseType.setHouseNum(resultSet.getInt(5));
                houseTypeList.add(houseType);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            setClear(resultSet);
        }
        return houseTypeList;
    }

    //多少条数据
    @Override
    public int count(int typeId,int condition,int roomNumber,String name) {
        String sql = "SELECT COUNT(1) FROM ORDER_MANAGEMENT O JOIN ROOM_KEEPER R ON O.ROOMNUMBER=R.ROOMNUMBER WHERE 1=1";
        StringBuffer sbf = new StringBuffer(sql);
        List<Object> param = new ArrayList<>();
        int count = -1;
        if (typeId > 0){
            sbf.append(" AND O.HOUSETYPEID = ?");
            param.add(typeId);
        }
        if (condition == -1 || condition > 0){
            sbf.append(" AND R.CONDITION = ?");
            param.add(condition);
        }
        if (condition == 0){
            sbf.append(" AND R.CONDITION > ?");
            param.add(condition);
        }
        if (roomNumber > 0){
            sbf.append(" AND O.ROOMNUMBER = ?");
            param.add(roomNumber);
        }
        if (name != null && !name.isEmpty()){
            sbf.append(" AND O.MANAGENAME LIKE CONCAT('%',?,'%')");
            param.add(name);
        }
        ResultSet resultSet = this.getResultSet(sbf.toString(),param);
        try {
            while (resultSet.next()){
                count = resultSet.getInt(1);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            setClear(resultSet);
        }
        return count;
    }

    //展示所以信息 根据房型,状态,房间号,姓名
    @Override
    public List<OrderManagement> seek(int typeId,int condition,int roomNumber,String name,int index,int page) {
        String sql = "SELECT O.ID,O.PASSWORD,O.MANAGENAME,O.CHECKINDATE,O.CHECKOUTDATE,O.MOBILEPHONE,O.IDENTITYCARD," +
                "O.PERSONNUM,O.HOUSETYPEID,O.PRICE,O.ROOMNUMBER,O.DISCOUNT,O.EARNINGS,O.REMARK,H.HOUSETYPE,R.CONDITION " +
                "FROM ORDER_MANAGEMENT O LEFT JOIN HOUSE_TYPE H ON O.HOUSETYPEID = H.ID LEFT JOIN ROOM_KEEPER R ON O.ROOMNUMBER " +
                "= R.ROOMNUMBER WHERE 1=1 ";
        StringBuffer sbf = new StringBuffer(sql);
        List<Object> param = new ArrayList<>();
        if (typeId > 0){
            sbf.append(" AND O.HOUSETYPEID = ?");
            param.add(typeId);
        }
        if (condition == -1 || condition > 0){
            sbf.append(" AND R.CONDITION = ?");
            param.add(condition);
        }
        if (condition == 0){
            sbf.append(" AND R.CONDITION > ?");
            param.add(condition);
        }
        if (roomNumber > 0){
            sbf.append(" AND O.ROOMNUMBER = ?");
            param.add(roomNumber);
        }
        if (name != null && !name.isEmpty()){
            sbf.append(" AND O.MANAGENAME LIKE CONCAT('%',?,'%')");
            param.add(name);
        }
        sbf.append(" ORDER BY O.CHECKINDATE LIMIT ?,?");
        param.add(index);
        param.add(page);
        ResultSet resultSet = this.getResultSet(sbf.toString(),param);
        List<OrderManagement> orderManagementList = new ArrayList<>();
        try {
            OrderManagement orderManagement = null;
            HouseType houseType = null;
            RoomKeeper roomKeeper = null;
            while (resultSet.next()){
                orderManagement = new OrderManagement();
                houseType = new HouseType();
                roomKeeper = new RoomKeeper();
                orderManagement.setId(resultSet.getLong(1));
                orderManagement.setPassword(resultSet.getString(2));
                orderManagement.setManageName(resultSet.getString(3));
                orderManagement.setCheckInDate(resultSet.getString(4));
                orderManagement.setCheckOutDate(resultSet.getString(5));
                orderManagement.setMobilePhone(resultSet.getString(6));
                orderManagement.setIdentityCard(resultSet.getString(7));
                orderManagement.setPersonNum(resultSet.getInt(8));
                orderManagement.setHouseTypeId(resultSet.getInt(9));
                orderManagement.setPrice(resultSet.getString(10));
                orderManagement.setRoomNumber(resultSet.getInt(11));
                orderManagement.setDiscount(resultSet.getInt(12));
                orderManagement.setEarnings(resultSet.getString(13));
                orderManagement.setRemark(resultSet.getString(14));
                houseType.setHouseType(resultSet.getString(15));
                orderManagement.setHouseType(houseType);
                roomKeeper.setCondition(resultSet.getInt(16));
                orderManagement.setRoomKeeper(roomKeeper);
                orderManagementList.add(orderManagement);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            setClear(resultSet);
        }
        return orderManagementList;
    }

    //删除订单 根据房间id
    @Override
    public int delete(Long id) {
        String sql = "DELETE FROM ORDER_MANAGEMENT WHERE ID = ?";
        List<Object> param = new ArrayList<>();
        param.add(id);
        int resultSet = this.getInt(sql,param);
        return resultSet;
    }

    //更新退房时间 根据房间号
    @Override
    public int setCheckOutDate(int roomNumber) {
        String sql = "UPDATE ORDER_MANAGEMENT SET CHECKOUTDATE = NOW() WHERE ROOMNUMBER = ?";
        List<Object> param = new ArrayList<>();
        param.add(roomNumber);
        int outDate = this.getInt(sql,param);
        return outDate;
    }

    //根据房号修改状态 根据房间状态和房间号码
    @Override
    public int setState(int condition, int roomNumber) {
        String sql = "UPDATE ROOM_KEEPER SET `CONDITION` = ? WHERE ROOMNUMBER = ?";
        List<Object> param = new ArrayList<>();
        param.add(condition);
        param.add(roomNumber);
        int amend = this.getInt(sql,param);
        return amend;
    }

    //新增订单
    @Override
    public int addClient(OrderManagement orderManagement) {
        String sql = "INSERT INTO ORDER_MANAGEMENT(PASSWORD,MANAGENAME,CHECKINDATE,CHECKOUTDATE,MOBILEPHONE," +
                "IDENTITYCARD,PERSONNUM,HOUSETYPEID,PRICE,ROOMNUMBER,REMARK) VALUES(?,?,?,?,?,?,?,?,?,?,?)";
        List<Object> param = new ArrayList<>();
        param.add(orderManagement.getPassword());
        param.add(orderManagement.getManageName());
        param.add(orderManagement.getCheckInDate());
        param.add(orderManagement.getCheckOutDate());
        param.add(orderManagement.getMobilePhone());
        param.add(orderManagement.getIdentityCard());
        param.add(orderManagement.getPersonNum());
        param.add(orderManagement.getHouseTypeId());
        param.add(orderManagement.getPrice());
        param.add(orderManagement.getRoomNumber());
        param.add(orderManagement.getRemark());
        int result = this.getInt(sql,param);
        return result;
    }

    //查空余房间 根据房间类型
    @Override
    public List<RoomKeeper> roomRates(int houseTypeId) {
        String sql = "SELECT R.ROOMNUMBER FROM ROOM_KEEPER R JOIN HOUSE_TYPE H ON R.HOUSETYPEID = H.ID " +
                "WHERE 1=1 AND R.HOUSETYPEID = ? AND R.CONDITION = 0 OR R.CONDITION = -1";
        List<Object> param = new ArrayList<>();
        param.add(houseTypeId);
        ResultSet resultSet = this.getResultSet(sql,param);
        List<RoomKeeper> roomKeeperList = null;
        try {
            roomKeeperList = new ArrayList<>();
            RoomKeeper roomKeeper = null;
            while (resultSet.next()){
                roomKeeper = new RoomKeeper();
                roomKeeper.setRoomNumber(resultSet.getInt(1));
                roomKeeperList.add(roomKeeper);
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return roomKeeperList;
    }

    //查房间价格 根据房间类型
    @Override
    public HouseType roomPrice(int houseTypeId) {
        String sql = "SELECT PRICE FROM HOUSE_TYPE WHERE ID = ? ";
        List<Object> param = new ArrayList<>();
        param.add(houseTypeId);
        ResultSet resultSet = this.getResultSet(sql,param);
        HouseType houseType = null;
        try {
            while (resultSet.next()){
             houseType = new HouseType();
             houseType.setPrice(resultSet.getString(1));
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            setClear(resultSet);
        }
        return houseType;
    }

    //根据手机号验证手机号是否已存在
    @Override
    public int phoneVerify(String mobilePhone) {
        String sql = "SELECT COUNT(MOBILEPHONE) FROM ORDER_MANAGEMENT WHERE MOBILEPHONE = ?";
        List<Object> param = new ArrayList<>();
        param.add(mobilePhone);
        ResultSet resultSet = this.getResultSet(sql,param);
        int count = 1;
        try {
            while (resultSet.next()){
                count = resultSet.getInt(1);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            setClear(resultSet);
        }
        return count;
    }

    //根据身份证号验证身份证号是否已存在
    @Override
    public int idCardVerify(String identityCard) {
        String sql = "SELECT COUNT(IDENTITYCARD) FROM ORDER_MANAGEMENT WHERE IDENTITYCARD = ?";
        List<Object> param = new ArrayList<>();
        param.add(identityCard);
        ResultSet resultSet = this.getResultSet(sql,param);
        int count = 1;
        try {
            while (resultSet.next()){
                count = resultSet.getInt(1);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            setClear(resultSet);
        }
        return count;
    }

    //查询指定信息 根据房间号
    @Override
    public OrderManagement check(int roomNumber) {
        String sql = "SELECT O.ID,O.PASSWORD,O.MANAGENAME,O.CHECKINDATE,O.CHECKOUTDATE,O.MOBILEPHONE,O.IDENTITYCARD," +
                "O.PERSONNUM,O.HOUSETYPEID,O.PRICE,O.ROOMNUMBER,O.DISCOUNT,O.REMARK,R.CONDITION FROM ORDER_MANAGEMENT O" +
                " JOIN ROOM_KEEPER R ON O.ROOMNUMBER = R.ROOMNUMBER WHERE O.ROOMNUMBER = ?";
        List<Object> param = new ArrayList<>();
        param.add(roomNumber);
        ResultSet resultSet = this.getResultSet(sql,param);
        OrderManagement orderManagement = null;
        try {
            RoomKeeper roomKeeper = null;
            while (resultSet.next()){
                orderManagement = new OrderManagement();
                roomKeeper = new RoomKeeper();
                orderManagement.setId(resultSet.getLong(1));
                orderManagement.setPassword(resultSet.getString(2));
                orderManagement.setManageName(resultSet.getString(3));
                orderManagement.setCheckInDate(resultSet.getString(4));
                orderManagement.setCheckOutDate(resultSet.getString(5));
                orderManagement.setMobilePhone(resultSet.getString(6));
                orderManagement.setIdentityCard(resultSet.getString(7));
                orderManagement.setPersonNum(resultSet.getInt(8));
                orderManagement.setHouseTypeId(resultSet.getInt(9));
                orderManagement.setPrice(resultSet.getString(10));
                orderManagement.setRoomNumber(resultSet.getInt(11));
                orderManagement.setDiscount(resultSet.getInt(12));
                orderManagement.setRemark(resultSet.getString(13));
                roomKeeper.setCondition(resultSet.getInt(14));
                orderManagement.setRoomKeeper(roomKeeper);
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return orderManagement;
    }

    //修改入住信息 根据房间id
    @Override
    public int revamp(OrderManagement orderManagement) {
        String sql = "UPDATE ORDER_MANAGEMENT SET MANAGENAME = ?,CHECKOUTDATE = ?,MOBILEPHONE = ?,IDENTITYCARD = ?," +
                "PERSONNUM = ?,HOUSETYPEID = ?,PRICE = ?,ROOMNUMBER = ?,DISCOUNT = ?,REMARK = ? WHERE ID = ?";
        List<Object> param = new ArrayList<>();
        param.add(orderManagement.getManageName());
        param.add(orderManagement.getCheckOutDate());
        param.add(orderManagement.getMobilePhone());
        param.add(orderManagement.getIdentityCard());
        param.add(orderManagement.getPersonNum());
        param.add(orderManagement.getHouseTypeId());
        param.add(orderManagement.getPrice());
        param.add(orderManagement.getRoomNumber());
        param.add(orderManagement.getDiscount());
        param.add(orderManagement.getRemark());
        param.add(orderManagement.getId());
        int up = this.getInt(sql,param);
        return up;
    }

    //会员验证
    @Override
    public Member vip(String mobilePhone) {
        String slq = "SELECT ACCOUNT,DISCOUNT FROM MEMBER WHERE ACCOUNT = ?";
        List<Object> param = new ArrayList<>();
        param.add(mobilePhone);
        Member member = null;
        ResultSet resultSet = this.getResultSet(slq,param);
        try {
            while (resultSet.next()){
                member = new Member();
                member.setAccount(resultSet.getString(1));
                member.setDiscount(resultSet.getDouble(2));
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            setClear(resultSet);
        }
        return member;
    }
}